package vendas.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vendas.dto.ActiveStatus;
import vendas.dto.Seller;
import vendas.dto.SellerType;


public class SellerDAO extends DataAccessObject {

    public static Seller findByPK(int sellerId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_SELLER       CD_SELLER    ");
        sbSql.append("     , A.TX_NAME         TX_NAME      ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE      ");
        sbSql.append("     , A.TX_WEB_NAME     TX_WEB_NAME  ");
        sbSql.append("     , A.TX_COMENTS      TX_COMENTS   ");
        sbSql.append("     , A.ID_ACTIVE       ID_ACTIVE    ");
        sbSql.append("  FROM TB_SELLER     A ");
        sbSql.append(" WHERE A.CD_SELLER = ? ");

        Seller seller = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, sellerId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                seller = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("SellerDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return seller;
    }

    public static List<Seller> findAll(Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_SELLER       CD_SELLER    ");
        sbSql.append("     , A.TX_NAME         TX_NAME      ");
        sbSql.append("     , A.ID_TYPE         ID_TYPE      ");
        sbSql.append("     , A.TX_WEB_NAME     TX_WEB_NAME  ");
        sbSql.append("     , A.TX_COMENTS      TX_COMENTS   ");
        sbSql.append("     , A.ID_ACTIVE       ID_ACTIVE    ");
        sbSql.append("  FROM TB_SELLER  A ");
        sbSql.append(" ORDER BY A.TX_NAME ");
        List<Seller> sellersList = new ArrayList<Seller>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Seller seller = fromResultSet(rs);
                sellersList.add(seller);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("SellerDAO.findAll (" + sqle.getMessage() + ")");
        }
        return sellersList;
    }

    public static void insert(Seller seller, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO TB_SELLER  ");
        sbSql.append("     ( CD_SELLER       ");
        sbSql.append("     , TX_NAME         ");
        sbSql.append("     , ID_TYPE         ");
        sbSql.append("     , TX_WEB_NAME     ");
        sbSql.append("     , TX_COMENTS      ");
        sbSql.append("     , ID_ACTIVE       ");
        sbSql.append(") VALUES (?,?,?,?,?,?)");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, seller.getId());
            setString(ps, pos++, seller.getName());
            setInt(ps, pos++, seller.getType().getId());
            setString(ps, pos++, seller.getWebUserName());
            setString(ps, pos++, seller.getComents());
            setInt(ps, pos++, seller.getActiveStatus().getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("SellerDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(Seller seller, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE TB_SELLER        ");
        sbSql.append("   SET TX_NAME      = ? ");
        sbSql.append("     , ID_TYPE      = ? ");
        sbSql.append("     , TX_WEB_NAME  = ? ");
        sbSql.append("     , TX_COMENTS   = ? ");
        sbSql.append("     , ID_ACTIVE    = ? ");
        sbSql.append(" WHERE CD_SELLER    = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, seller.getName());
            setInt(ps, pos++, seller.getType().getId());
            setString(ps, pos++, seller.getWebUserName());
            setString(ps, pos++, seller.getComents());
            setInt(ps, pos++, seller.getActiveStatus().getId());
            setInt(ps, pos++, seller.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("SellerDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(Seller seller, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM TB_SELLER ");
        sbSql.append(" WHERE CD_SELLER = ?  ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, seller.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("SellerDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Connection conn, int iRange, int eRange) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_SELLER) AS CD_MAX ");
            sbSql.append("  FROM TB_SELLER     ");
            sbSql.append(" WHERE CD_SELLER > ? ");
            sbSql.append("   AND CD_SELLER < ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, iRange);
            setInt(ps, 2, eRange);
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return (max != 0 ? max : iRange) + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static Seller fromResultSet(ResultSet rs) throws SQLException {

        // TB_SELLER
        Seller seller = new Seller();
        seller.setId(getInt(rs, "CD_SELLER"));
        seller.setName(getString(rs, "TX_NAME"));
        seller.setType(SellerType.getSellerTypeById(getByte(rs, "ID_TYPE")));
        seller.setWebUserName(getString(rs, "TX_WEB_NAME"));
        seller.setComents(getString(rs, "TX_COMENTS"));
        seller.setActiveStatus(ActiveStatus.getActiveStatusById(getByte(rs, "ID_ACTIVE")));
        //
        return seller;
    }

}
